package club.banyuan.SpringBootHrms.repository.impl;

import club.banyuan.SpringBootHrms.repository.AdminDao;
import club.banyuan.SpringBootHrms.model.Admin;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.List;

@Repository
public class JdbcTemplateAdminDaoImpl implements AdminDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;


    @Override
    public List<Admin> getAdmins() {
        return null;
    }

    /**
     * 分页查询
     * <p>
     * BeanPropertyRowMapper<>(Admin.class)
     * 取得列表的方法
     *
     * @param pageNum
     * @param pageSize
     * @return
     */
    @Override
    public List<Admin> getAdmins(Integer pageNum, Integer pageSize) {
        Integer offset = (pageNum - 1) * pageSize;
        String sql = "SELECT * FROM t_admin LIMIT ?,?";
        List<Admin> admins = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Admin.class), offset, pageSize);
        return admins;
    }

    @Override
    public Admin getAdminByUsernameAndPassword(String username, String password) {
        String sql = "SELECT * FROM t_admin WHERE username=? AND password=?";
        List<Admin> admins = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Admin.class), username, password);
        if (admins.size() > 0) {
            return admins.get(0);
        } else {
            return null;
        }
    }

    @Override
    public void addAdmin(Admin admin) {
        String sql = "INSERT INTO t_admin(username,rolename,password) VALUES (?,?,?)";

        // 不需要获得主键
//        jdbcTemplate.update(sql,admin.getUsername(),admin.getRolename(),admin.getPassword());

        KeyHolder keyHolder = new GeneratedKeyHolder();
        // 回调 获取PreparedStatement，目的是可以得到主键
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                preparedStatement.setString(1, admin.getUsername());
                preparedStatement.setString(2, admin.getRolename());
                preparedStatement.setString(3, admin.getPassword());
                return preparedStatement;
            }
        }, keyHolder);
        admin.setId(keyHolder.getKey().intValue());
    }

    @Override
    public void updateAdmin(Admin admin) {
        String sql = "UPDATE t_admin SET username=?,rolename=?,password=? WHERE id=?";
        jdbcTemplate.update(sql, admin.getUsername(), admin.getRolename(), admin.getPassword(), admin.getId());
    }

    /**
     * 调用jdbcTemplate.query 查询语言 这里 三个参数 sql rowMapper id
     * rowMapper 是一个取得返回集的匿名内部类，id是sql语句的占位符
     * 返回值的list集合
     *
     * @param id
     * @return
     */
    @Override
    public Admin getAdminById(Integer id) {
        String sql = "SELECT * FROM t_admin WHERE id=?";

        List<Admin> admins = jdbcTemplate.query(sql, new RowMapper<Admin>() {
            @Override
            public Admin mapRow(ResultSet resultSet, int i) throws SQLException {
                Admin admin = new Admin();
                admin.setId(resultSet.getInt("id"));
                admin.setUsername(resultSet.getString("username"));
                admin.setRolename(resultSet.getString("rolename"));
                admin.setPassword(resultSet.getString("password"));
                return admin;
            }
        }, id);
        if (admins.size() > 0) {
            return admins.get(0);
        } else {
            return null;
        }
    }

    @Override
    public void deleteAdmin(Integer[] ids) {
        String sql = "DELETE FROM t_admin WHERE id IN (?";
        for (int i = 1; i < ids.length; i++) {
            sql += ",?";
        }
        sql += ")";
        String finalSql = sql;
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement preparedStatement = connection.prepareStatement(finalSql);
                for (int i = 0; i < ids.length; i++) {
                    preparedStatement.setInt(i + 1, ids[i]);
                }
                return preparedStatement;
            }
        });
    }

    @Override
    public void deleteAdmin(String username) {
        String sql = "DELETE FROM t_admin WHERE username=?";
        jdbcTemplate.update(sql, username);
    }
}
